﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Restaurant.DAO
{
    public class NhaCungCapDB
    {
        private SqlConnection conn = KetNoiDB.TaoKetNoi();

        public DataTable LoadTatCaNhaCungCap()
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spLoadTatCaNCC";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LoadHanThanhToan()
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spLoadHanThanhToan";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool ThemNhaCungCap(string ten, string diachi,string sdt, int mahtt)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spThemNCC";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@tenNCC", ten));
                cmd.Parameters.Add(new SqlParameter("@diaChi", diachi));
                cmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                cmd.Parameters.Add(new SqlParameter("@maHTT", mahtt));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool SuaNhaCungCap(int ma, string ten, string diachi, string sdt, int mahtt)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spSuaNCC";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNCC", ma));
                cmd.Parameters.Add(new SqlParameter("@tenNCC", ten));
                cmd.Parameters.Add(new SqlParameter("@diaChi", diachi));
                cmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                cmd.Parameters.Add(new SqlParameter("@maHTT", mahtt));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool XoaNhaCungCapID(int ma)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spXoaNCC";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNCC", ma));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DataTable TimNCCID(int ma)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spTimNCCID";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNCC", ma));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return dbtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable TimNCCTen(string ten)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spTimNCCTen";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@tenNCC", ten));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return dbtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable TimHanTTTheoTen(string ten)
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spTimHTTTheoTen";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ten", ten));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable TimHanTTTheoMa(int ma)
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spTimHTTTheoMa";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ma", ma));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LoadNCCDirtyreadError()
        {
            conn.Open();
            string strCmd = "dbo.spDirtyRead_TimNCC_Error";
            SqlCommand cmd = new SqlCommand(strCmd, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtab = new DataTable();
            adapt.Fill(dbtab);
            conn.Close();
            return dbtab;
        }

        public DataTable LoadNCCDirtyread()
        {
            conn.Open();
            string strCmd = "dbo.spDirtyRead_TimNCC_Fixed";
            SqlCommand cmd = new SqlCommand(strCmd, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtab = new DataTable();
            adapt.Fill(dbtab);
            conn.Close();
            return dbtab;
        }

        public bool SuaNCCDirtyreadError(int ma, string ten, string diachi, string sdt, int mahtt)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spDirtyRead_SuaNCC_Error";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNCC", ma));
                cmd.Parameters.Add(new SqlParameter("@tenNCC", ten));
                cmd.Parameters.Add(new SqlParameter("@diaChi", diachi));
                cmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                cmd.Parameters.Add(new SqlParameter("@maHTT", mahtt));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool SuaNCCDirtyread(int ma, string ten, string diachi, string sdt, int mahtt)
        {
            try
            {
                conn.Open();
                string strcmd = "dbo.spDirtyRead_SuaNCC_Fixed";
                SqlCommand cmd = new SqlCommand(strcmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNCC", ma));
                cmd.Parameters.Add(new SqlParameter("@tenNCC", ten));
                cmd.Parameters.Add(new SqlParameter("@diaChi", diachi));
                cmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                cmd.Parameters.Add(new SqlParameter("@maHTT", mahtt));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DataTable LoadNCCUnrepeatError(ref int soluong)
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spUnrepeatable_TimNCC_Error";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@soluong", soluong));
                cmd.Parameters["@soluong"].Direction = ParameterDirection.Output;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                soluong = int.Parse(cmd.Parameters["@soluong"].Value.ToString());
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LoadNCCUnrepeat(ref int soluong)
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spUnrepeatable_TimNCC_Fixed";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@soluong", soluong));
                cmd.Parameters["@soluong"].Direction = ParameterDirection.Output;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                soluong = int.Parse(cmd.Parameters["@soluong"].Value.ToString());
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LoadNCCPhantomError(ref int soluong)
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spPhantom_TimNCC_Error";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@soluong", soluong));
                cmd.Parameters["@soluong"].Direction = ParameterDirection.Output;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                soluong = int.Parse(cmd.Parameters["@soluong"].Value.ToString());
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LoadNCCPhantom(ref int soluong)
        {
            try
            {

                conn.Open();
                string strCmd = "dbo.spPhantom_TimNCC_Fixed";
                SqlCommand cmd = new SqlCommand(strCmd, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@soluong", soluong));
                cmd.Parameters["@soluong"].Direction = ParameterDirection.Output;
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtab = new DataTable();
                adapt.Fill(dbtab);
                soluong = int.Parse(cmd.Parameters["@soluong"].Value.ToString());
                conn.Close();
                return dbtab;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
